/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dbclasses;

import classes.Car;
import classes.Card;
import classes.Category;
import classes.Customer;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;


/**
 * Class with methods that perform predefined select queries on the database.
 * @author keaneb1
 */
public class Doselect {
    private static Connection conn = null;
    private static Statement st = null;
   
    /**
     * Method to query the database for the unique car locations
     * @return the list of locations in an ArrayList
     */
    public static List<String> getUniqueLocations() {
        //create a new ArrayList object and get a new connection from the Dbconnector class
        ArrayList<String> result = new ArrayList<>();
        conn = Dbconnector.getConnected();
        
        if(conn!=null){
            try{
                st = conn.createStatement();
                
                //query the database for the unique locations of cars, fill the results into the ArrayList
                ResultSet rs = st.executeQuery("SELECT DISTINCT location FROM location ORDER BY location;");
                while (rs.next()) {
                    result.add(rs.getString("location"));
                }
                //close out the statement and connection when finished
                st.close();
                conn.close();
                
            } catch (SQLException ex) {
                //tell the client which method call errored out and what was the message
                JOptionPane.showMessageDialog(null, "Problem in getUniqueLocations " +ex.getSQLState());
            }
        }
        //return the ArrayList to the client
        return result;
    }
    
    /**
     * Method to query the database for a unique list of car categories
     * @return the list of car categories in an ArrayList.
     */
    public static List<String> getCategories() {
        //create a new ArrayList object and get a new connection from the Dbconnector class
        ArrayList<String> result = new ArrayList<>();
        conn = Dbconnector.getConnected();
        
        if(conn!=null){
            try{
                st = conn.createStatement();
                
                //get the car categories from the database and into the ArrayList
                ResultSet rs = st.executeQuery("SELECT * FROM car_category ORDER BY daily_rate;");
                while (rs.next()) {
                result.add(rs.getString("category"));
            }
            //close out the statement and connection when finished
            st.close();
            conn.close();
            
            } catch (SQLException ex) {
                //tell the client which method call errored out and what was the message
                JOptionPane.showMessageDialog(null, "Problem in getCategories " +ex.getSQLState());
            }
        }
        //return the Arraylist to the client
        return result;
    }

        /**
         * Method to return the cars by category and location that are available for the selected dates
         * @param location the selected location of the car
         * @param category the selected category of car requested
         * @param fromDate the date when the booking is to commence
         * @param toDate the date when the booking is to end
         * @return the list of cars available
         */
    public static List<Car> getCarsByLocationCategory(String location, String category, Date fromDate, Date toDate) {
        //create a new ArrayList object and get a new connection from the Dbconnector class
        ArrayList<Car> result = new ArrayList<>();
        conn = Dbconnector.getConnected();
        
        //proceed only if there is a valid connection
        if(conn!=null){
            try {
                st = conn.createStatement();

                /*
                 * the following query returns only cars in the selected location and category where the selected fromDate and toDate
                 * are either before the startDate in the current booking or after the endDate in the current bookings.
                 * The client application ensures you can only select a date after the current date.
                 */
                
                String queryStr ="select * from car c, car_category x where c.car_category=x.category and c.car_category='"+category+"' and c.location='"+location+"' "
                + "and c.reg_no not in (select reg_no from car c, booking b where c.reg_no=b.car and ((b.start_date > '"+fromDate+"' AND "
                + "b.start_date > '"+toDate+"') or (b.return_date < '"+fromDate+"' and b.return_date < '"+toDate+"')));";
                
                //get the query results, build the category and car objects and fill the ArrayList
                ResultSet rs = st.executeQuery(queryStr);
                while (rs.next()) {
                    Category newCategory = new Category(rs.getString("car_category"),rs.getDouble("daily_rate"));
                    Car car = new Car(rs.getString("reg_no"),newCategory,rs.getString("location"));
                    result.add(car);
                }
                //close out the statement and the connection
                st.close();
                conn.close();
            
            } catch (SQLException ex) {
                //tell the client which method call errored out and what was the message
                JOptionPane.showMessageDialog(null, "Problem in getCarsByLocationCategory " +ex.getSQLState());
            }
        }
        //return the ArrayList to the client
        return result;
    }
    
    /**
     * Method to return the price of cars per selected category
     * @param category the category of cars selected
     * @return the price of the selected car category per day
     */
    public static double getPricePerCategory(String category){
        //get a new connection and continue only if a valid connection is returned.
        double price = 0.00;
        conn = Dbconnector.getConnected();
        
        if(conn!=null){
            try {
                st = conn.createStatement();
                
                //query the database for the daily rate from the car_category table
                ResultSet rs = st.executeQuery("Select daily_rate FROM car_category WHERE category='"+category+"'");
                while (rs.next()) {
                    price = rs.getDouble("daily_rate");
                }
                
                //close the statement and connection
                st.close();
                conn.close();
            
            } catch (SQLException ex) {
                //tell the client which method call errored out and what was the message
                JOptionPane.showMessageDialog(null, "Problem in getPricePerCategory " +ex.getSQLState());
            }
        }
        //return the daily rate of that car category
        return price;
    }
    
    /**
     * Method that returns all the customers or just those based on a search string if entered
     * @param searchString the string entered by the user to shorten the customer list
     * @return the list of customers matched by the query
     */
    public static List<Customer> getCustomers(String searchString){
        //create a new ArrayList for Cuatomer objects
        ArrayList<Customer> result = new ArrayList<>();
        String queryStr;
        
        //build the query string depending on if the user entered a search string or not.
        if("".equals(searchString)){
            queryStr = "SELECT * FROM customer c, creditcard z WHERE c.creditcardno=z.creditcardno;";
        }
        else queryStr = "SELECT * FROM customer c, creditcard z WHERE c.creditcardno=z.creditcardno AND c.creditcardno LIKE '"+searchString+"%';";
        
        //open a connection and continue if a good connection is received from Dbconnector
        conn = Dbconnector.getConnected();
        if(conn!=null){
            try{
                st = conn.createStatement();
                
                //query the database and create new Card and Customer objects for each row returned. Add to the ArrayList
                ResultSet rs = st.executeQuery(queryStr);
                while (rs.next()){
                    Card tmpCard = new Card(rs.getString("creditcardno"),rs.getString("personname"),rs.getString("type"),rs.getString("cardexpiry"),rs.getInt("csv"));
                    Customer tmpCustomer = new Customer(rs.getString("firstname"),rs.getString("lastname"),rs.getString("email"),rs.getString("licenseno"),rs.getDate("expirydate"),tmpCard);
                    result.add(tmpCustomer);
                }
                
                //close out the statement and connection
                st.close();
                conn.close();
            }
            catch(SQLException ex){
                //tell the client which method call errored out and what was the message
                JOptionPane.showMessageDialog(null, "Problem in getCustomers " +ex.getSQLState());
            }
        }
        //return the ArrayList of customers to the client
        return result;
    }
}
